# -*- coding: UTF-8 -*-
"""
Created on 2016/10/29

@author: kenny.huang

"""

import MySQLdb
import traceback
from MySQLdb.cursors import DictCursor
from DBUtils.PooledDB import PooledDB
#from PooledDB import PooledDB
import lamp_config
from logger import logger
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

"""
lamp_config
"""

class Mysql(object):
    """
    MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现获取连接对象：conn = Mysql.getConn()
            释放连接对象;conn.close()或del conn
    """
    #连接池对象
    __pool = None
    def __init__(self):
        if Mysql.__pool is None:
            db_name = lamp_config.getConfig("database", "dbname")
            db_host = lamp_config.getConfig("database", "dbhost")
            db_port = lamp_config.getConfig("database", "dbport")
            db_user = lamp_config.getConfig("database", "dbuser")
            db_password = lamp_config.getConfig("database", "dbpassword")
            db_charset = lamp_config.getConfig("database", "dbcharset")
            Mysql.__pool = PooledDB(
                creator=MySQLdb,\
                mincached=5,\
                maxcached=1000,\
                maxconnections=100,\
                host=db_host,\
                user=db_user,\
                passwd=db_password,\
                db=db_name,\
                charset='utf8',\
                port=int(db_port),\
                setsession=['SET AUTOCOMMIT = 1']\
                )
                #["SET NAMES utf8","SET CHARACTER SET utf8"])
            Mysql.__pool.charset='utf8'
            logger.info("init Mysql pool successed!!!!")


    @staticmethod
    def __getConn():
        if Mysql.__pool is None:
            return None
        return Mysql.__pool.connection()

    def getAll(self,sql,param=None):
        """
        @summary: 执行查询，并取出所有结果集
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result dist
        """
        connection = Mysql.__getConn()
        cursor = connection.cursor()
        if isinstance(sql, unicode):
            sql = sql.encode("utf8")
        result = []
        #logger.error(sql)
        try:
            if param is None:
                count = cursor.execute(sql)
            else:
                if isinstance(param, unicode):
                    param = param.encode("utf8")
                count = cursor.execute(sql, param)
            if count > 0:
                result = self.dictfetchall(cursor)
                #result = cursor.fetchall()
            #connection.commit()
        except Exception,exception:
            result = []
            logger.info(sql)
            logger.error(traceback.format_exc())
        finally:
            if cursor != None:
                cursor.close()
            if connection != None:
                connection.close()
        return result

    def insertOne(self,sql,value=None):
        """
        @summary: 向数据表插入一条记录
        @param sql:要插入的ＳＱＬ格式
        @param value:要插入的记录数据tuple/list
        @return: insertId 受影响的行数
        """
        connection = Mysql.__getConn()
        cursor = connection.cursor()
        #cursor.execute("SET NAMES utf8")
        #cursor.execute("SET CHARACTER SET utf8")
        if isinstance(sql, unicode):
            sql = sql.encode("utf8")
        #logger.info(sql)
        try:
            if value is None:
                cursor.execute(sql)
            else:
                if isinstance(value, unicode):
                    value = value.encode("utf8")
                cursor.execute(sql,value)

            #connection.commit()
        except Exception,exception:
            connection.rollback()
            logger.info(sql)
            logger.error(traceback.format_exc())
        finally:
            if cursor != None:
                cursor.close()
            if connection != None:
                connection.close()

    def insertMany(self, sql, values=None):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的ＳＱＬ格式
        @param values:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        connection = Mysql.__getConn()
        cursor = connection.cursor()
        count = 0
        if isinstance(sql, unicode):
            sql = sql.encode("utf8")
        #logger.info(sql)
        try:
            if values is None:
                count = cursor.executemany(sql)
            else:
                if isinstance(values, unicode):
                    values = values.encode("utf8")
                count = cursor.executemany(sql, values)

            #connection.commit()
        except Exception,exception:
            connection.rollback()
            logger.error(traceback.format_exc())
        finally:
            if cursor != None:
                cursor.close()
            if connection != None:
                connection.close()

        return count

    def __query(self,sql,param=None):
        connection = Mysql.__getConn()
        cursor = connection.cursor()
        if isinstance(sql, unicode):
            sql = sql.encode("utf8")
        count = 0
        #logger.info(sql)
        try:
            if param is None:
                count = cursor.execute(sql)
            else:
                if isinstance(param, unicode):
                    param = param.encode("utf8")
                count = cursor.execute(sql,param)
            #connection.commit()
        except Exception,exception:
            connection.rollback()
            logger.error(traceback.format_exc())
        finally:
            if cursor != None:
                cursor.close()

            if connection != None:
                connection.close()

        return count

    def __queryMany(self,sql,param=None):
        connection = Mysql.__getConn()
        cursor = connection.cursor()
        if isinstance(sql, unicode):
            sql = sql.encode("utf8")
        count = 0
        #logger.info(sql)
        try:
            if param is None:
                count = cursor.executemany(sql)
            else:
                if isinstance(param, unicode):
                     param = param.encode("utf8")
                count = cursor.executemany(sql,param)
            #connection.commit()
        except Exception,exception:
            connection.rollback()
            logger.error(traceback.format_exc())
        finally:
            if cursor != None:
                cursor.close()

            if connection != None:
                connection.close()

        return count

    def dictfetchall(self, cursor):
        "Returns all rows from a cursor as a dict"
        desc = cursor.description
        return [
            dict(zip([col[0] for col in desc], row))
            for row in cursor.fetchall()
        ]


    def update(self,sql,param=None):
        """
        @summary: 更新数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql,param)

    def updateMany(self,sql,param=None):
        """
        @summary: 更新数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__queryMany(sql,param)


    def delete(self,sql,param=None):
        """
        @summary: 删除数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql,param)
